---1

--create DIRECTORY if NOT EXISTS DIR_EXT AS '/dm8/backup/dir_ext';
/*
create EXTERNAL TABLE t_extEMP (
"EMPLOYEE_ID" INT ,
"EMPLOYEE_NAME" VARCHAR(20),
"IDENTITY_CARD" VARCHAR(18),
"EMAIL" VARCHAR(50) ,
"PHONE_NUM" VARCHAR(20),
"HIRE_DATE" DATE ,
"JOB_ID" VARCHAR(10) ,
"SALARY" INT,
"COMMISSION_PCT" INT,
"MANAGER_ID" INT,
"DEPARTMENT_ID" INT 
) FROM DATAFILE DEFAULT DIRECTORY DIR_EXT
LOCATION('mycity.txt') PARMS(fields ',',records 0x0a ,skip 1, character_code 'UTF-8')
*/


--select * from DMHR.EMPLOYEE
--select * from DMHR.DEPARTMENT
--select * from T_EXTEMP

--2


/*create VIEW IF NOT EXISTS V_EMPTOP10DEPT AS 
select DEPARTMENT_ID,DEPARTMENT_NAME,avg_sal,rand_id
from(
	SELECT t.DEPARTMENT_ID ,d.DEPARTMENT_NAME,
	avg(t.SALARY) as avg_sal ,
	rank() over(order by avg(t.SALARY) desc) rand_id 
	FROM  t_extEMP t  inner JOIN DMHR.DEPARTMENT d ON (t.DEPARTMENT_ID=d.DEPARTMENT_ID)
	group BY (T.DEPARTMENT_ID , d.DEPARTMENT_NAME )
	HAVING avg(t.SALARY)>8000 

)
where rand_id <11*/



--select * from V_EMPTOP10DEPT


--3


/*create TABLE T_stuscore (
subjectid int ,--CHECK( ) COMMENT '学科编码'  
stuid int,
score int
) 
*/
/*insert into T_STUSCORE(subjectid,T_STUSCORE.STUID,T_STUSCORE.SCORE)
VALUES(subjectid,T_STUSCORE.STUID,score)
select mod(level-1,5)+1 subjectid,
float(trunc((level-1)/5)+1) stuid,
round(dbms_random.value(50,150)) score
from dua
connect by rownum <=1000*/

--select * from T_STUSCORE
/*CREATE TABLE T_SUBJECT(
SUBJECTID INT,
SUBJECTNAME VARCHAR(20)
)
*/
/*select * from T_SUBJECT
alter TABLE T_SUBJECT ADD CONSTRAINT PK_SUBJECTID PRIMARY KEY(SUBJECTID) 
truncate table T_SUBJECT
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(1,'语文');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(2,'数学');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(3,'英语');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(4,'理科综合');
insert into t_subject(SUBJECTID,SUBJECTNAME) VALUES(5,'文科综合');
rollback
commit*/

--select * from dual  connect by level <100
--方法一
/*INSERT INTO T_STUSCORE( T_STUSCORE.SUBJECTID,T_STUSCORE.STUID,T_STUSCORE.SCORE) 
SELECT MOD(level,5)+1 as SUBJECTID , ROUND(level/5)+1 as STUID,FLOOR(DBMS_RANDOM.VALUE(150,50)) AS score
from DUAL CONNECT BY ROWNUM <=1000;*/
--方法二
/*BEGIN 
	FOR i IN 1..5 LOOP
		FOR j in 1..20 LOOP
			INSERT INTO T_STUSCORE(SUBJECTID,T_STUSCORE.STUID,T_STUSCORE.SCORE) 
				VALUES(i,j , FLOOR(DBMS_RANDOM.value(150,100)));
		 END LOOP;
	END Loop;
	commit;
end

*/

/*TRUNCATE TABLE T_STUSCORE
select * from T_STUSCORE*/
--

--4
/*select *  from  T_STUSCORE*/
/*create or REPLACE VIEW V_STUSCORETOP AS
SELECT T_STUSCORE.STUID ,sum(T_STUSCORE.SCORE), RANK() OVER(order by sum(T_STUSCORE.SCORE) desc) as rank from T_STUSCORE GROUP BY T_STUSCORE.STUID ORDER BY sum(T_STUSCORE.SCORE) desc  LIMIT 10
commit
*/

--5
--create index idx_stscore_SUBJECTID on T_STUSCORE(SUBJECTID)
CREATE INDEX idx_stucoretop_name ON V_STUSCORETOP (STUID)


